package com.leadingsoft.pwxk.excel;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class ExcelUtil {
	public ExcelUtil() {

	}

	public static final String KEY_FIELD = "fieldName";
	public static final String KEY_CELL_TYPE = "cellType";

	/**
	 * 生成Excel,并以输出流返回到前端
	 *
	 * @param fileName
	 *            文件名
	 * @param displayNames
	 *            Excel字段,与keys按顺序一一对应
	 * @param keys
	 *            对应数据源对象的属性
	 * @param datas
	 *            数据源
	 * @param c
	 *            数据源中的对象的class
	 * @param response
	 * @param callBack回调，对excel进一步处理,可为null
	 * @return
	 */
	public static HSSFWorkbook createWorkBook(String fileName, String[] displayNames, String[] keys, List datas,
			Class c, HttpServletResponse response, IExcelCallBack callBack, IDataCallBack dataCallback) {
		Font borderFont = null;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(fileName);
		borderFont = workbook.createFont();
		ExportExcel exportExcel = new ExportExcel(workbook, sheet);

		// 计算该数据表的列数
		for (int i = 0; i < displayNames.length; i++) {
			if (i == 0) {
				exportExcel.setColumnsWidthStyle(sheet, i, 3000);
			} else {
				exportExcel.setColumnsWidthStyle(sheet, i, 6000);
			}
		}
		// 设置标题行
		exportExcel.setHeadLine(displayNames, 0);
		CellStyle style = createTrueBorderCellStyle(workbook, HSSFColor.WHITE.index, HSSFColor.WHITE.index,
				HSSFCellStyle.ALIGN_CENTER, borderFont);
		style.setWrapText(true);
		OutputStream fOut = null;
		try {
			Field[] fs = c.getDeclaredFields();
			short rowNumber = 0;
			for (Object object : datas) {
				rowNumber++;
				HSSFRow row = sheet.createRow((short) rowNumber);
				row.setHeight((short) 800);
				borderFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
				for (int i = 0; i < keys.length; i++) {
					for (Field field : fs) {
						field.setAccessible(true); // 设置些属性是可以访问的
						if (keys[i].equals(field.getName())) {
							HSSFCell cell = row.createCell(i);
							cell.setCellType(Cell.CELL_TYPE_STRING);
							String key = keys[i];
							String val = field.get(object) == null ? "" : field.get(object).toString();
							val = dataCallback == null ? val : dataCallback.run(key, val);
							cell.setCellValue(new HSSFRichTextString(val));
							cell.setCellStyle(style);
						}

					}

				}
			}
			if (callBack != null) {
				callBack.run(exportExcel);
			}

			response.reset();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");
			response.setHeader("Content-Disposition",
					"attachment; filename=" + (new String(fileName.getBytes(), "iso-8859-1")) + ".xls");

			fOut = response.getOutputStream();
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(fOut);
		}
		return workbook;

	}

	/**
	 * 生成Excel,并以输出流返回到前端
	 *
	 * @param fileName
	 *            文件名
	 * @param displayNames
	 *            Excel字段,与keys按顺序一一对应
	 * @param keys
	 *            对应数据源对象的属性
	 * @param datas
	 *            数据源
	 * @param response
	 * @param callBack回调，对excel进一步处理,可为null
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static HSSFWorkbook createWorkBook(String fileName, String[] displayNames, String[] keys,
			List<Map<String, Object>> datas, HttpServletResponse response, IExcelCallBack callBack,
			IDataCallBack dataCallback) {

		Font borderFont = null;
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet(fileName);
		borderFont = workbook.createFont();
		ExportExcel exportExcel = new ExportExcel(workbook, sheet);

		// 计算该数据表的列数
		for (int i = 0; i < displayNames.length; i++) {
			if (i == 0) {
				exportExcel.setColumnsWidthStyle(sheet, i, 3000);
			} else {
				exportExcel.setColumnsWidthStyle(sheet, i, 5000);
			}
		}
		// 设置标题行
		exportExcel.setHeadLine(displayNames, 0);
		CellStyle style = createTrueBorderCellStyle(workbook, HSSFColor.WHITE.index, HSSFColor.WHITE.index,
				HSSFCellStyle.ALIGN_CENTER, borderFont);
		style.setWrapText(true);
		OutputStream fOut = null;
		try {
			short rowNumber = 0;
			for (Map<String, Object> map : datas) {
				rowNumber++;
				HSSFRow row = sheet.createRow((short) rowNumber);
				row.setHeight((short) 800);
				borderFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
				for (int i = 0; i < keys.length; i++) {
					HSSFCell cell = row.createCell(i);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String key = keys[i].toString();
					String val = map.get(key) == null ? "" : map.get(key).toString();
					val = dataCallback == null ? val : dataCallback.run(key, val);
					cell.setCellValue(new HSSFRichTextString(val));
					cell.setCellStyle(style);
				}
			}

			if (callBack != null) {
				callBack.run(exportExcel);
			}

			response.reset();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");
			response.setHeader("Content-Disposition",
					"attachment; filename=" + (new String(fileName.getBytes(), "iso-8859-1")) + ".xls");

			fOut = response.getOutputStream();
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(fOut);
		}
		return workbook;
	}

	/**
	 * 生成Excel,并以输出流返回到前端 支持表头多行
	 *
	 * @param fileName
	 *            文件名
	 * @param titles
	 *            表头 可有多行
	 * @param keys
	 *            对应数据源对象的属性
	 * @param datas
	 *            数据源
	 * @param response
	 *            返回处理
	 * @param callBack，对excel进一步处理,可为null
	 * @param dataCallback，数据进行处理,可为null
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static HSSFWorkbook createWorkBook(String fileName, List<String[]> titles, String[] keys,
			List<Map<String, Object>> datas, HttpServletResponse response, IExcelCallBack callBack,
			IDataCallBack dataCallback) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		// HSSFSheet sheet = workbook.createSheet(fileName);
		HSSFSheet sheet = workbook.createSheet();
		Font borderFont = workbook.createFont();
		ExportExcel exportExcel = new ExportExcel(workbook, sheet);
		// 处理表头
		if (titles != null && titles.size() > 0) {
			// 计算该数据表的列数
			for (int i = 0; i < titles.get(0).length; i++) {
				if (i == 0) {
					exportExcel.setColumnsWidthStyle(sheet, i, 3000);
				} else {
					exportExcel.setColumnsWidthStyle(sheet, i, 5000);
				}
			}
			for (int i = 0; i < titles.size(); i++) {
				if (i == 0) {
					String[] displayNames = titles.get(i);
					// 设置标题行
					exportExcel.setFirstHeadLine(displayNames, i);
				} else {
					String[] displayNames = titles.get(i);
					// 设置标题行
					exportExcel.setHeadLine(displayNames, i);
				}
			}
		}

		// 处理数据内容
		CellStyle style = createTrueBorderCellStyle(workbook, HSSFColor.WHITE.index, HSSFColor.WHITE.index,
				HSSFCellStyle.ALIGN_CENTER, borderFont);
		style.setWrapText(true);
		OutputStream fOut = null;
		try {
			short rowNumber = 0;
			if (CollectionUtils.isNotEmpty(titles)) {
				rowNumber = (short) titles.size();
			}
			for (Map<String, Object> map : datas) {
				HSSFRow row = sheet.createRow(rowNumber++);
				row.setHeight((short) 800);
				borderFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
				for (int i = 0; i < keys.length; i++) {
					HSSFCell cell = row.createCell(i);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String key = keys[i];
					String val = map.get(key) == null ? "" : map.get(key).toString();
					val = dataCallback == null ? val : dataCallback.run(key, val);
					cell.setCellValue(new HSSFRichTextString(val));
					cell.setCellStyle(style);
				}
			}

			if (callBack != null) {
				callBack.run(exportExcel);
			}

			response.reset();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");
			response.setHeader("Content-Disposition",
					"attachment; filename=" + (new String(fileName.getBytes(), "iso-8859-1")) + ".xls");

			fOut = response.getOutputStream();
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(fOut);
		}
		return workbook;
	}

	/**
	 * 生成Excel,并以输出流返回到前端 支持表头多行
	 *
	 * @param fileName
	 *            文件名
	 * @param titles
	 *            表头 可有多行
	 * @param keys
	 *            对应数据源对象的属性
	 * @param datas
	 *            数据源
	 * @param response
	 *            返回处理
	 * @param callBack，对excel进一步处理,可为null
	 * @param dataCallback，数据进行处理,可为null
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static HSSFWorkbook epsCreateWorkBook(String fileName, List<String[]> titles, String[] keys,
			List<Map<String, Object>> datas, HttpServletResponse response, IExcelCallBack callBack,
			IDataCallBack dataCallback) {

		HSSFWorkbook workbook = new HSSFWorkbook();
		// HSSFSheet sheet = workbook.createSheet(fileName);
		HSSFSheet sheet = workbook.createSheet();
		Font borderFont = workbook.createFont();
		ExportExcel exportExcel = new ExportExcel(workbook, sheet);
		// 处理表头
		if (titles != null && titles.size() > 0) {
			// 计算该数据表的列数
			for (int i = 0; i < titles.get(0).length; i++) {
				if (i == 0) {
					exportExcel.setColumnsWidthStyle(sheet, i, 5000);
				} else {
					exportExcel.setColumnsWidthStyle(sheet, i, 10000);
				}
			}
			for (int i = 0; i < titles.size(); i++) {
				if (i == 0) {
					String[] displayNames = titles.get(i);
					// 设置标题行
					exportExcel.setFirstHeadLine(displayNames, i);
				} else {
					String[] displayNames = titles.get(i);
					// 设置标题行
					exportExcel.setHeadLine(displayNames, i);
				}
			}
		}

		// 处理数据内容
		CellStyle style = createTrueBorderCellStyle(workbook, HSSFColor.WHITE.index, HSSFColor.WHITE.index,
				HSSFCellStyle.ALIGN_CENTER, borderFont);
		style.setWrapText(true);
		OutputStream fOut = null;
		try {
			short rowNumber = 0;
			if (CollectionUtils.isNotEmpty(titles)) {
				rowNumber = (short) titles.size();
			}
			for (Map<String, Object> map : datas) {
				HSSFRow row = sheet.createRow(rowNumber++);
				row.setHeight((short) 800);
				borderFont.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
				for (int i = 0; i < keys.length; i++) {
					HSSFCell cell = row.createCell(i);
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String key = keys[i];
					String val = map.get(key) == null ? "" : map.get(key).toString();
					val = dataCallback == null ? val : dataCallback.run(key, val);
					cell.setCellValue(new HSSFRichTextString(val));
					cell.setCellStyle(style);
				}
			}

			if (callBack != null) {
				callBack.run(exportExcel);
			}

			response.reset();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Type", "application/vnd.ms-excel;charset=UTF-8");
			response.setHeader("Content-Disposition",
					"attachment; filename=" + (new String(fileName.getBytes(), "iso-8859-1")) + ".xls");

			fOut = response.getOutputStream();
			workbook.write(fOut);
			fOut.flush();
			fOut.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			IOUtils.closeQuietly(fOut);
		}
		return workbook;
	}

	private static CellStyle createTrueBorderCellStyle(HSSFWorkbook wb, short backgroundColor, short foregroundColor,
			short halign, Font borderFont) {
		CellStyle cs = wb.createCellStyle();
		cs.setAlignment(halign);
		cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
		cs.setFillBackgroundColor(backgroundColor);
		cs.setFillForegroundColor(foregroundColor);
		cs.setFillPattern(CellStyle.SOLID_FOREGROUND);
		cs.setFont(borderFont);
		cs.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		cs.setBorderRight(HSSFCellStyle.BORDER_THIN);
		cs.setBorderTop(HSSFCellStyle.BORDER_THIN);
		cs.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		return cs;
	}

	/**
	 * 
	 * @param file
	 *            文件
	 * @param dataLine
	 *            起始行
	 * @param sheetNum
	 *            sheet数量
	 * @return
	 * @throws IOException
	 */
	public static List<Map<Integer, List<String>>> parseExcel(MultipartFile file, int dataLine, int sheetNum)
			throws IOException {
		List<Map<Integer, List<String>>> dataList = new ArrayList<Map<Integer, List<String>>>();
		Workbook wb = null;
		String fileName = file.getOriginalFilename();
		try {
			if (!validateExcel(fileName)) {
				log.info("上传文件格式异常");
				return null;
			}
			wb = new HSSFWorkbook(file.getInputStream());
			/*
			 * if (isExcel2003(fileName)) { wb = new
			 * XSSFWorkbook(file.getInputStream()); } if (isExcel2007(fileName))
			 * { wb = new HSSFWorkbook(file.getInputStream()); }
			 */
		} catch (OfficeXmlFileException e) {
			log.error("系统异常：" + e.getMessage());
		}
		// 8个sheet
		for (int m = 0; m < sheetNum; m++) {
			Map<Integer, List<String>> content = new HashMap<Integer, List<String>>();
			Sheet sheet = wb.getSheetAt(m);
			// 得到总行数
			int rowNum = sheet.getLastRowNum();
			// 数据从第dataLine行开始
			Row row = sheet.getRow(dataLine);
			int colNum = row.getPhysicalNumberOfCells();
			// 数据从第dataLine行开始,前面都是列头
			for (int i = dataLine; i <= rowNum; i++) {
				row = sheet.getRow(i);
				if (isRowEmpty(row)) {
					continue;
				}
				List<String> list = new ArrayList<String>();
				for (int j = 0; j < colNum; j++) {
					Cell cell = row.getCell(j);
					if (cell != null) {
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_NUMERIC: // 数字
							if (HSSFDateUtil.isCellDateFormatted(cell)) {
								// 如果是date类型则 ，获取该cell的date值
								list.add(DateFormatUtils.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()),
										"yyyy/MM/dd"));
							} else { // 纯数字
								double d = cell.getNumericCellValue();
								if (d - (int) d < Double.MIN_VALUE) {
									// 是否为int型
									list.add(Integer.toString((int) d));
								} else {
									// 是否为double型
									list.add(Double.toString(cell.getNumericCellValue()));
								}
							}
							break;
						case HSSFCell.CELL_TYPE_STRING: // 字符串
							list.add(cell.getStringCellValue() + "");
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
							list.add(cell.getBooleanCellValue() + "");
							break;
						case HSSFCell.CELL_TYPE_FORMULA: // 公式
							list.add(cell.getCellFormula() + "");
							break;
						case HSSFCell.CELL_TYPE_BLANK: // 空值
							list.add("");
							break;
						case HSSFCell.CELL_TYPE_ERROR: // 故障
							list.add("");
							break;
						default:
							list.add("");
							break;
						}
					} else {
						list.add("");
					}
				}
				if (!isLast(row)) {
					content.put(i, list);
				} else {
					content.put(-1, list);
					break;
				}
			}
			dataList.add(content);
		}
		return dataList;
	}

	/**
	 * 验证EXCEL文件
	 * 
	 * @param filePath
	 * @return
	 */
	public static boolean validateExcel(String fileName) {
		if (fileName == null || !(isExcel2003(fileName) || isExcel2007(fileName))) {
			log.info("文件名不是excel格式");
			return false;
		}
		return true;
	}

	// @描述：是否是2003的excel，返回true是2003
	public static boolean isExcel2003(String fileName) {
		return fileName.matches("^.+\\.(?i)(xls)$");
	}

	// @描述：是否是2007的excel，返回true是2007
	public static boolean isExcel2007(String fileName) {
		return fileName.matches("^.+\\.(?i)(xlsx)$");
	}

	/**
	 * 
	 * @param row
	 * @return 判断是否空行
	 */
	public static boolean isRowEmpty(Row row) {
		if (row != null) {
			for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
				Cell cell = row.getCell(c);
				if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
					return false;
				}
			}
		}
		return true;
	}

	public static boolean isLast(Row row) {
		if (row != null) {
			Cell cell = row.getCell(0);
			if (null != cell && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
				String data = cell.getStringCellValue();
				if (data.indexOf("填报人") != -1 && data.indexOf("联系方式") != -1) {
					return true;
				}
			}
		}
		return false;
	}
}
